/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package server;

//<editor-fold defaultstate="collapsed" desc=" các thư viện sử dụng">
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.*;
import org.w3c.dom.*;
import org.xml.sax.SAXException;


//</editor-fold>

/**
 *
 * @author KingofSpeed
 */
public class ToolSQL {
    private MainWindow main;
    public ToolSQL( MainWindow _main )
    {
        main = _main;
    }
    //<editor-fold defaultstate="collapsed" desc=" đổi tên bảng thành số thứ tự trong list">
    public int NameToIndex( String tableName )
    {
        for ( int i = 0; i < main.dataTables.size(); i++ )
        {
            if ( main.dataTables.get(i).getName().equals(tableName))
                return i;
        }
        return -1;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" đổi tên cột thành số thứ tự trong list">
    public int TitleToIndex( int tIndex, String title )
    {
        for ( int i = 0; i < main.dataTables.get(tIndex).getnColumn(); i++)
        {
            if ( main.dataTables.get(tIndex).getColumns().get(i).getTitle().equals(title))
                return i;
        }
        return -1;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem cột có khóa ngoại ko">
    public boolean IsHaveForeignKey( int tIndex1, int tIndex2 )
    {
        for ( int i = 0;i < main.dataTables.get(tIndex1).getnColumn(); i++)
        {
            for ( int j = 0; j < main.dataTables.get(tIndex1).getColumns().get(i).getRef().size(); j++)
            {
                String tName = GetNameFromForeignKey( main.dataTables.get(tIndex1).getColumns().get(i).getRef().get(j), true  );
                if ( tName.equals(main.dataTables.get(tIndex2).getName()))
                    return true;
            }
        }
        return false;
    }

    public boolean IsHaveForeignKey( String tName1, String tName2 )
    {
        return IsHaveForeignKey( NameToIndex(  tName1 ), NameToIndex(  tName2 ) );
    }
    public int TitleToIndex( String tName, String title )
    {
        return ( TitleToIndex( NameToIndex( tName),title ));
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra các ràng buộc để xem có thể xóa bảng được không">
    public boolean CanDelete( int n )
    {
        if ( n == -1 )
            return false;
        for ( int i = 0; i < main.dataTables.size(); i++  )
        {
            if ( i != n && IsHaveForeignKey( i, n))
            {
                    main.ShowMessageBox("Bảng "+ main.dataTables.get(i).getName() + " Có Khóa Ngoại Trỏ Đến Bảng Này, Không Thể Xóa, Muốn Xóa Hãy Bỏ Khóa Ngoại Đi Trước"
                            , "Error");
                    return false;
            }
        }
        return true;
    }
    public boolean CanDelete( String tName )
    {
        int n = NameToIndex( tName);
        return CanDelete(n);
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" lấy tên bảng hoặc tên cột từ SQL query">
    public String GetNameFromSelectQuery( String key, boolean getTableName )
    {
        String result[];
        if ( !key.contains("."))
            return key;
        try
        {
            result = key.split("\\.");
         //    main.ShowMessageBox(key, "");
            if ( getTableName == true )
                return result[0];
            else if ( getTableName == false )
                return result[1];
            return key;
        }
        catch ( Exception ex ){ return key;}
                
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" lấy tên bảng hoặc tên cột từ khóa ngoại">
    public String GetNameFromForeignKey( String key, boolean getTableName )
    {
        String result[];
        try
        {
                result = key.split("_");
                if ( getTableName == true )
                    return result[0];
                else if ( getTableName == false )
                    return result[1];
            return "";
        }
        catch ( Exception ex ){ return "";}
                
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" xóa một cột ra khỏi bảng, xóa luôn các khóa ngoại trỏ đến nó">
    public boolean DeleteColumn( String tName, String colName )
    {
        return DeleteColumn( NameToIndex( tName), colName );
    }
    public boolean DeleteColumn( int tIndex , int colIndex )
    {
        return DeleteColumn( tIndex, main.dataTables.get(tIndex).getColumns().get(colIndex).getTitle() );
    }
    public boolean DeleteColumn( int tIndex, String colName )
    {
        try
        {
            if ( tIndex < 0 )
                return false;
            String tName = main.dataTables.get(tIndex).getName();
            for ( int i = 0; i < main.dataTables.size(); i++ )
            {
                if ( i != tIndex )
                {
                    for ( int j = 0; j < main.dataTables.get(i).getnColumn(); j++ )
                    {
                        for ( int k = 0; k < main.dataTables.get(i).getColumns().get(j).getRef().size(); k++ )
                        {
                            String tab = GetNameFromForeignKey(  main.dataTables.get(i).getColumns().get(j).getRef().get(k), true);
                            String col = GetNameFromForeignKey(  main.dataTables.get(i).getColumns().get(j).getRef().get(k), false);
                            if ( tab.equals(tName) && col.equals(colName) )
                            {
                                main.dataTables.get(i).getColumns().get(j).getRef().remove(k);
                                k--;
                            }
                        }
                    }
                }
            }
            return true;
        }
        catch( Exception ex ){ return false;}
    }
    public boolean DeleteColumn( String tName, int colIndex  )
    {
        int i = NameToIndex( tName);
        if ( i < 0 )
            return false;
        return DeleteColumn( i , main.dataTables.get(i).getColumns().get(colIndex).getTitle());
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" đổi tên bảng phải đổi luôn các khóa ngoại ở các cột trỏ tới bảng này">
    public boolean RenameTable( String oldName, String newName )
    {
        try
        {
            for ( int i = 0; i < main.dataTables.size(); i++ )
            {
                for ( int j = 0; j < main.dataTables.get(i).getnColumn(); j++)
                {
                    for ( int k = 0; k < main.dataTables.get(i).getColumns().get(j).getRef().size(); k++)
                    {
                        if (GetNameFromForeignKey( main.dataTables.get(i).getColumns().get(j).getRef().get(k), true).equals(oldName))
                        {
                           
                            String col = GetNameFromForeignKey( main.dataTables.get(i).getColumns().get(j).getRef().get(k), false);
                            main.dataTables.get(i).getColumns().get(j).getRef().set(k, newName + "_" + col) ;
                        }
                    }
                }
                main.dataTables.get(i).SaveForeignKeyChange(i);
                
            }
            return true;
        }
        catch ( Exception ex ){ return false ;}
        
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" xóa các cột trong bảng">
    public boolean DeleteColumns(String tName, ArrayList<String> columnsToDelete )
    {
        try
        {
            for ( int i = 0; i < main.dataTables.size(); i++ )
            {
                for ( int j = 0; j < main.dataTables.get(i).getnColumn(); j++)
                {
                    for ( int k = 0; k < main.dataTables.get(i).getColumns().get(j).getRef().size(); k++)
                    {
                        if (GetNameFromForeignKey( main.dataTables.get(i).getColumns().get(j).getRef().get(k), true).equals(tName))
                        {
                            String col = GetNameFromForeignKey( main.dataTables.get(i).getColumns().get(j).getRef().get(k), false);
                            if ( columnsToDelete.contains(col))
                            {
                                main.dataTables.get(i).getColumns().get(j).getRef().remove(k) ;
                                main.dataTables.get(i).SaveForeignKeyChange(j);
                                k--;
                            }
                        }
                    }
                }
            }
            return true;
        }
        catch ( Exception ex ){ return false ;}
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" tìm vị trí của một chuỗi trong list">
    public int FindPosition( String name, ArrayList<String> tam )
    {
        for ( int i = 0; i < tam.size(); i++ )
        {
            if ( tam.get(i).equals(name))
                return i;
        }
        return -1;
    }
    public int FindPosition( String name, List<String> tam )
    {
        for ( int i = 0; i < tam.size(); i++ )
        {
            
            if ( tam.get(i).equals(name))
            {            
                return i;
            }
        }
        return -1;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" đổi tên cột đổi luôn tên ở các khóa ngoại">
    public boolean RenameColumns(String tName, ArrayList<String> columnsToRename)
    {
        
        try
        {
            ArrayList<String> oldKey = new ArrayList<String>();
            ArrayList<String> newKey = new ArrayList<String>();
            for ( int i = 0 ; i < columnsToRename.size(); i++ )
            {
                oldKey.add(tName + "_" + GetNameFromForeignKey( columnsToRename.get(i), true));
                newKey.add(tName + "_" + GetNameFromForeignKey( columnsToRename.get(i), false));
            }
            for ( int i = 0; i < main.dataTables.size(); i++ )
            {
                for ( int j = 0; j < main.dataTables.get(i).getnColumn(); j++)
                {
                    for ( int k = 0; k < main.dataTables.get(i).getColumns().get(j).getRef().size(); k++)
                    {
                            String key = main.dataTables.get(i).getColumns().get(j).getRef().get(k);
                            int pos = FindPosition( key, oldKey);
                            if ( pos >=  0 )
                            {
                                main.dataTables.get(i).getColumns().get(j).getRef().set(k, newKey.get(pos)) ;
                                main.dataTables.get(i).SaveForeignKeyChange(j);
                            }

                    }
                }
            }
            return true;
        }
         catch ( Exception ex ){ return false ;}
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem có khóa ngoại nào trỏ tới không">
    public boolean CheckForeignKeyFrom( String tName, int iRow, int iCol )
    {
        int tIndex = NameToIndex(tName);
        String value = String.valueOf(main.dataTables.get(tIndex).getModel().getValueAt(iRow, iCol));
        String colName = main.dataTables.get(tIndex).getColumns().get(iCol).getTitle();
        
        for ( int i = 0; i < main.dataTables.size(); i++ )
        {
            for ( int j = 0; j < main.dataTables.get(i).getnColumn(); j++ )
            {
                int pos = FindPosition( tName + "_" + colName, main.dataTables.get(i).getColumns().get(j).getRef());      
                if ( pos  >= 0 )
                {
                    for ( int k = 0; k < main.dataTables.get(i).getModel().getRowCount(); k++)
                    {
                        String v = String.valueOf(main.dataTables.get(i).getModel().getValueAt(k, j));
                        if ( v.equals(value))
                        {
                            return false;
                        }
                    }
                }
            }
        }
        return true;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem có trỏ tới bảng nào không">
    public boolean CheckForiegnKeyTo( String tName, int iRow, int iCol )
    {
        int tIndex = NameToIndex(tName);
        String value = String.valueOf(main.dataTables.get(tIndex).getModel().getValueAt(iRow, iCol));
        String colName = main.dataTables.get(tIndex).getColumns().get(iCol).getTitle();
        
        for ( int i = 0; i < main.dataTables.get(tIndex).getColumns().get(iCol).getRef().size(); i++)
        {
            String key = main.dataTables.get(tIndex).getColumns().get(iCol).getRef().get(i);
            String name = GetNameFromForeignKey( key, true );
            String cName = GetNameFromForeignKey(key, false);
            int table = NameToIndex( name);
            if ( table < 0 )
                return false;
            int column = TitleToIndex( table, cName);
            if ( table < 0 || column < 0 )
                continue;
            boolean tonTai = false;
            for ( int j = 0 ; j < main.dataTables.get(table).getModel().getRowCount(); j++)
            {
                if ( String.valueOf(main.dataTables.get(table).getModel().getValueAt(j, column)).equals(value))
                {
                    tonTai = true;
                    break;
                }
            }
            if ( tonTai == false )
            {
                
                return false;
            }
        }
        return true;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem chuỗi có phải là số không">
    public boolean CheckIsNum( String name )
    {
        try
        {
            int countDot = 0;
            for ( int i = 0; i < name.length();i++)
            {
                if ( name.charAt(0) != '-' && (name.charAt(i) < '0' ||name.charAt(i) > '9' ) && name.charAt(i) != '.')
                {
                    return false;
                }
                if ( name.charAt(i) == '.')
                {
                    countDot++;
                    if ( countDot > 1)
                        return false;
                }
            }
            return true;
        }
        catch( Exception ex ){  return false;}
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem chuỗi có chứa kí tự bị cấm không">
    public boolean CheckIsString( String name )
    {
        try
        {
            for ( int i = 0; i < name.length();i++)
            {
                if  (name.charAt(i) == '&' ||name.charAt(i) == '\\' ||name.charAt(i) == '\"'
                        ||name.charAt(i) == '\'' ||name.charAt(i) == '<' )
                {
                    return false;
                }
            }
            return true;
        }
        catch( Exception ex ){  return false;}
    }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Tìm Min ">
    public String Min(String type ,ArrayList<String> data)
    {
        if ( data.size() == 0)
            return "";
       
        if ( type.equals("Number"))
        {
            double result = Double.parseDouble(data.get(0));
            for ( int i = 0; i < data.size(); i++)
            {
                double tam = Double.parseDouble(data.get(i));
                if ( tam < result)
                    result = tam;
            }
            return String.valueOf(result);
        }
        else
        {
            String result = data.get(0);
            for ( int i = 0; i < data.size(); i++)
            {
                if ( result.compareTo(data.get(i)) > 0 )
                    result = data.get(i);
            }
            return result;
        }
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" Tìm Max">
    public String Max(String type ,ArrayList<String> data)
    {
        if ( data.size() == 0)
            return "";
       
        if ( type.equals("Number"))
        {
            double result = Double.parseDouble(data.get(0));
            for ( int i = 0; i < data.size(); i++)
            {
                double tam = Double.parseDouble(data.get(i));
                if ( tam > result)
                    result = tam;
            }
            return String.valueOf(result);
        }
        else
        {
            String result = data.get(0);
            for ( int i = 0; i < data.size(); i++)
            {
                if ( result.compareTo(data.get(i)) < 0 )
                    result = data.get(i);
            }
            return result;
        }
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" Copy Chuỗi Bảng ( Clone )">
    public DefaultTableModel Clone( DefaultTableModel model )
    {
        DefaultTableModel result = new DefaultTableModel();
        try
        {
                ArrayList col = new ArrayList();
                for ( int i = 0; i < model.getColumnCount(); i++ )
                {
                    String colName = model.getColumnName(i);
                    String tam = " (not null)";
                    if ( colName.endsWith(tam))
                    {
                        colName = colName.substring(colName.length() - tam.length()-1,colName.length()-1 );
                    }
                    col.add(colName);
                }
                result= new DefaultTableModel(null ,col.toArray());
                for ( int i = 0; i < model.getRowCount(); i++ )
                {
                    ArrayList row = new ArrayList();
                    for ( int k = 0; k < model.getColumnCount(); k++ )
                    {
                        Object a = model.getValueAt(i, k);
                        row.add(a);
                    }
                    result.addRow(row.toArray());
                }
        }
        catch( Exception ex )
        {
        }
        return result;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc=" lấy vị trí cột trong model">
    public int ColToIndex( String col, DefaultTableModel  model )
    {
        if( !col.contains("."))
        {
            for ( int i = 0; i < model.getColumnCount(); i++)
            {
               
                if ( this.GetNameFromSelectQuery( model.getColumnName(i), false ).equals(col) 
                        || this.GetNameFromSelectQuery( model.getColumnName(i), false ).equals(col + " (not null)") )
                    return i;
            }
        }
        else if( col.contains("."))
        {
         //   main.ShowMessageBox("1", "");
            for ( int i = 0; i < model.getColumnCount(); i++)
            {
             //    main.ShowMessageBox( model.getColumnName(i) +"_" + col,"");
                if ( model.getColumnName(i).equals(col) 
                        ||  model.getColumnName(i).equals(col + " (not null)") )
                    return i;
            }
        }
        return -1;
    }
     //</editor-fold >
    //    //<editor-fold defaultstate="collapsed" desc=" lấy vị trí cột và vị trí model chứa cột trong list model">
//    public ArrayList<Integer>  ColToIndexFromListModel( String col, ArrayList<DefaultTableModel> listModel )
//    {
//        ArrayList<Integer>  result = new ArrayList<Integer>(Arrays.asList(new Integer[]{-1, -1}));
//        for ( int i = 0;i < listModel.size(); i++ )
//        {
//            int index = this.ColToIndex(col, listModel.get(i));
//            if ( index != -1 )
//            {
//                result.set(0, i);
//                result.set(1, index);
//            }
//        }
//        return result;
//    }
//     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" lấy danh sách dữ liệu từ một cột trong model ">
    public ArrayList<String> GetDataInColumnFromModel( int index, DefaultTableModel model )
    {
        ArrayList<String> result = new ArrayList<String>();
        for ( int i = 0; i < model.getRowCount(); i++)
        {
            try
            {
                result.add(String.valueOf(model.getValueAt(i, index)));
            }
            catch( Exception ex ){}
        }
        return result;
    }
     //</editor-fold >
 
    //<editor-fold defaultstate="collapsed" desc=" Lấy Danh Sách bảng">
    public ArrayList<DefaultTableModel> From( ArrayList<String> tableNames)
    {
        ArrayList<DefaultTableModel> models = new ArrayList<DefaultTableModel>();
        for ( int i = 0; i < tableNames.size(); i++ )
        {
            int index = this.NameToIndex(tableNames.get(i));
            if ( index >= 0 )
                models.add(main.dataTables.get(index).getModel());
        }
        return models;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" so sánh bằng">
    public DefaultTableModel Equal( String left, String right, DefaultTableModel model, String type )
    {
        if ( type.equals("Value_Value"))
        {
            if( !left.equals(right))
                model.setRowCount(0);
            return model;
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model );
            
            if ( pos1 < 0 )
            {
                model.setRowCount(0);
                return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                  
                    if ( !right.equals(dataInCol1.get(i)))
                    {
                       
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --; 
                       
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model );
            int pos2 = this.ColToIndex(right, model );
           // main.ShowMessageBox(String.valueOf(pos1) + "_" +String.valueOf(pos2) , left);
            if ( pos1 == -1 || pos2 == -1)
            {
                 model.setRowCount(0);
                 return model;
            }
            for ( int i = 0; i < model.getRowCount(); i++)
            {
                try
                {
                    String tam2 = String.valueOf( model.getValueAt(i, pos1));
                    String tam = String.valueOf( model.getValueAt(i, pos2));
                    
                    if ( !tam2.equals(tam))
                    {
                        model.removeRow(i);
                        i--;
                    }
//                    else
//                        main.ShowMessageBox(tam + "_" + tam2, tam);
                }
                catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
            }
        }
        return model;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" so sánh không bằng">
    public DefaultTableModel NotEqual( String left, String right, DefaultTableModel model, String type )
    {
        if ( type.equals("Value_Value"))
        {
            if( left.equals(right))
                model.setRowCount(0);
            return model;
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model );
             if ( pos1 == -1 )
            {
                 model.setRowCount(0);
                 return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                    if ( right.equals(dataInCol1.get(i)))
                    {
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --;
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model );
            int pos2 = this.ColToIndex(right, model );
            if ( pos1 == -1 || pos2 == -1)
            {
                 model.setRowCount(0);
                 return model;
            }
            for ( int i = 0; i < model.getRowCount(); i++)
            {
                try
                {
                    String tam2 = String.valueOf( model.getValueAt(i, pos1));
                    String tam = String.valueOf( model.getValueAt(i, pos2));
                    if ( tam2.equals(tam))
                    {
                        model.removeRow(i);
                        i--;
                    }
                }
                catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
            }
        }
        return model;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" so sánh lớn hơn">
    public DefaultTableModel GreaterThan( String left, String right, DefaultTableModel model, String type )
    {
        
        if ( type.equals("Value_Value"))
        {
            if ( CheckIsNum(left) && CheckIsNum(right))
            {
                double a = Double.parseDouble(left);
                double b = Double.parseDouble(right);
                if ( a <= b )
                    model.setRowCount(0);
                return model;
            }
            else
            {
                if( left.compareTo(right)<=0)
                    model.setRowCount(0);
                return model;
            }            
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model);
            if ( pos1 ==  - 1)
            {
                model.setRowCount(0);
                return model;
            }
            int index = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex = this.TitleToIndex(index,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ = main.dataTables.get(index).getColumns().get( colIndex).getType();
            if ( typ.equals("Number")&& CheckIsNum(right))
            {
                ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
                double v = Double.parseDouble(right);
                for ( int i = 0; i < dataInCol1.size(); i++)
                {
                    try
                    {
                        if ( Double.parseDouble(dataInCol1.get(i)) <= v)
                        {
                            model.removeRow(i);
                            dataInCol1.remove(i);
                            i --;
                        }
                    }
                    catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                    }
                }
                return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                    if ( right.compareTo(dataInCol1.get(i)) >= 0)
                    {
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --;
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model);
            int pos2 = this.ColToIndex(right, model);
            int index1 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex1 = this.TitleToIndex(index1,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ1 = main.dataTables.get(index1).getColumns().get( colIndex1).getType();
            int index2 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos2), true));
            int colIndex2 = this.TitleToIndex(index2,this.GetNameFromSelectQuery(model.getColumnName(pos2), false) );
            String typ2 = main.dataTables.get(index2).getColumns().get( colIndex2).getType();
            if ( pos1 == -1 || pos2 == -1 || !typ1.equals(typ2))
            {
                 model.setRowCount(0);
                 return model;
            }
            if ( typ1.equals("Number"))
            {
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( Double.parseDouble(tam) >= Double.parseDouble(tam2) )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
            }
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( tam.compareTo(tam2) >= 0  )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
        }
        return model;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" so sánh nhỏ hơn">
    public DefaultTableModel LessThan( String left, String right, DefaultTableModel model,  String type )
    {
        if ( type.equals("Value_Value"))
        {
            if ( CheckIsNum(left) && CheckIsNum(right))
            {
                double a = Double.parseDouble(left);
                double b = Double.parseDouble(right);
                if ( a >= b )
                    model.setRowCount(0);
                return model;
            }
            else
            {
                if( left.compareTo(right) >= 0)
                    model.setRowCount(0);
                return model;
            }            
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model);
            if ( pos1 ==  - 1)
            {
                model.setRowCount(0);
                return model;
            }
            int index = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex = this.TitleToIndex(index,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ = main.dataTables.get(index).getColumns().get( colIndex).getType();
            if ( typ.equals("Number")&& CheckIsNum(right))
            {
                ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
                double v = Double.parseDouble(right);
                for ( int i = 0; i < dataInCol1.size(); i++)
                {
                    try
                    {
                        if ( Double.parseDouble(dataInCol1.get(i)) >= v)
                        {
                            model.removeRow(i);
                            dataInCol1.remove(i);
                            i --;
                        }
                    }
                    catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                    }
                }
                return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                    if ( right.compareTo(dataInCol1.get(i)) >= 0)
                    {
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --;
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model);
            int pos2 = this.ColToIndex(right, model);
            int index1 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex1 = this.TitleToIndex(index1,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ1 = main.dataTables.get(index1).getColumns().get( colIndex1).getType();
            int index2 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos2), true));
            int colIndex2 = this.TitleToIndex(index2,this.GetNameFromSelectQuery(model.getColumnName(pos2), false) );
            String typ2 = main.dataTables.get(index2).getColumns().get( colIndex2).getType();
            if ( pos1 == -1 || pos2 == -1 || !typ1.equals(typ2))
            {
                 model.setRowCount(0);
                 return model;
            }
            if ( typ1.equals("Number"))
            {
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( Double.parseDouble(tam) >= Double.parseDouble(tam2) )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
            }
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( tam.compareTo(tam2) >= 0  )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
               return model;
        }
        return model;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" so sánh lớn hơn hoặc bằng">
  public DefaultTableModel GreaterThanOrEqal( String left, String right, DefaultTableModel model, String type )
    {
        if ( type.equals("Value_Value"))
        {
            if ( CheckIsNum(left) && CheckIsNum(right))
            {
                double a = Double.parseDouble(left);
                double b = Double.parseDouble(right);
                if ( a < b )
                    model.setRowCount(0);
                return model;
            }
            else
            {
                if( left.compareTo(right)<0)
                    model.setRowCount(0);
                return model;
            }            
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model);
            if ( pos1 ==  - 1)
            {
                model.setRowCount(0);
                return model;
            }
            int index = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex = this.TitleToIndex(index,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ = main.dataTables.get(index).getColumns().get( colIndex).getType();
            if ( typ.equals("Number")&& CheckIsNum(right))
            {
                ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
                double v = Double.parseDouble(right);
                for ( int i = 0; i < dataInCol1.size(); i++)
                {
                    try
                    {
                        if ( Double.parseDouble(dataInCol1.get(i)) < v)
                        {
                            model.removeRow(i);
                            dataInCol1.remove(i);
                            i --;
                        }
                    }
                    catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                    }
                }
                return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                    if ( right.compareTo(dataInCol1.get(i)) > 0)
                    {
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --;
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model);
            int pos2 = this.ColToIndex(right, model);
            int index1 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex1 = this.TitleToIndex(index1,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ1 = main.dataTables.get(index1).getColumns().get( colIndex1).getType();
            int index2 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos2), true));
            int colIndex2 = this.TitleToIndex(index2,this.GetNameFromSelectQuery(model.getColumnName(pos2), false) );
            String typ2 = main.dataTables.get(index2).getColumns().get( colIndex2).getType();
            if ( pos1 == -1 || pos2 == -1 || !typ1.equals(typ2))
            {
                 model.setRowCount(0);
                 return model;
            }
            if ( typ1.equals("Number"))
            {
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( Double.parseDouble(tam) > Double.parseDouble(tam2) )
                        {
                             model.removeRow(i);
                             i --;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
            }
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( tam.compareTo(tam2) > 0  )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
        }
        return model;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" so sánh nhỏ hơn hoặc bằng">
  public DefaultTableModel LessThanOrEqual( String left, String right, DefaultTableModel model,  String type )
    {
        if ( type.equals("Value_Value"))
        {
            if ( CheckIsNum(left) && CheckIsNum(right))
            {
                double a = Double.parseDouble(left);
                double b = Double.parseDouble(right);
                if ( a > b )
                    model.setRowCount(0);
                return model;
            }
            else
            {
                if( left.compareTo(right) > 0)
                    model.setRowCount(0);
                return model;
            }            
        }
        else if ( type.equals("Column_Value"))
        {
            int pos1 = this.ColToIndex(left, model);
            if ( pos1 ==  - 1)
            {
                model.setRowCount(0);
                return model;
            }
            int index = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex = this.TitleToIndex(index,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ = main.dataTables.get(index).getColumns().get( colIndex).getType();
            if ( typ.equals("Number")&& CheckIsNum(right))
            {
                ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
                double v = Double.parseDouble(right);
                for ( int i = 0; i < dataInCol1.size(); i++)
                {
                    try
                    {
                        if ( Double.parseDouble(dataInCol1.get(i)) > v)
                        {
                            model.removeRow(i);
                            dataInCol1.remove(i);
                            i --;
                        }
                    }
                    catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                    }
                }
                return model;
            }
            ArrayList<String> dataInCol1 = this.GetDataInColumnFromModel(pos1, model);
            for ( int i = 0; i < dataInCol1.size(); i++)
            {
                try
                {
                    if ( right.compareTo(dataInCol1.get(i)) > 0)
                    {
                        model.removeRow(i);
                        dataInCol1.remove(i);
                        i --;
                    }
                }
                catch( Exception ex ){ main.ShowMessageBox("1. " + ex.toString(), "");
                }
            }
            return model;
        }
        else if ( type.equals("Column_Column"))
        {
            int pos1 = this.ColToIndex(left, model);
            int pos2 = this.ColToIndex(right, model);
            int index1 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos1), true));
            int colIndex1 = this.TitleToIndex(index1,this.GetNameFromSelectQuery(model.getColumnName(pos1), false) );
            String typ1 = main.dataTables.get(index1).getColumns().get( colIndex1).getType();
            int index2 = this.NameToIndex(this.GetNameFromSelectQuery(model.getColumnName(pos2), true));
            int colIndex2 = this.TitleToIndex(index2,this.GetNameFromSelectQuery(model.getColumnName(pos2), false) );
            String typ2 = main.dataTables.get(index2).getColumns().get( colIndex2).getType();
            if ( pos1 == -1 || pos2 == -1 || !typ1.equals(typ2))
            {
                 model.setRowCount(0);
                 return model;
            }
            if ( typ1.equals("Number"))
            {
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( Double.parseDouble(tam) > Double.parseDouble(tam2) )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
            }
               for ( int i = 0; i < model.getRowCount(); i++)
                {
                    try
                    {
                        String tam = String.valueOf(model.getValueAt(i, pos1));
                        String tam2 = String.valueOf(model.getValueAt(i, pos2));
                        if ( tam.compareTo(tam2) > 0  )
                        {
                            model.removeRow(i);
                            i--;
                        }
                    }
                    catch( Exception ex ){main.ShowMessageBox("1. " + ex.toString(), "");}
                }
               return model;
        }
        return model;
    }
     //</editor-fold >
 
  
    //<editor-fold defaultstate="collapsed" desc=" EXISTS " >
     public DefaultTableModel  Exists( DefaultTableModel  model, DefaultTableModel reference )
     {
         if ( reference.getRowCount() ==  0)
             model.setRowCount(0);
         return model;
     }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc="NOT EXISTS " >
     public DefaultTableModel  NotExists( DefaultTableModel  model , DefaultTableModel reference)
     {
         if ( reference.getRowCount() !=  0)
             model.setRowCount(0);
         return model;
     }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc="NOT IN " >
     public DefaultTableModel NotIn( String left, DefaultTableModel model, DefaultTableModel reference, String type)
     {
         try
         {
            if ( reference.getColumnCount() == 0 )
                return model;
            if ( type.equals("Column_Model"))
            {
                ArrayList<String> data = this.GetDataInColumnFromModel(0, reference);
                int index = this.ColToIndex(left, model);
                ArrayList<String> data2 = this.GetDataInColumnFromModel(index, reference);
                for ( int i = 0; i < data2.size();i++)
                {
                    if ( data.contains(data2.get(i)))
                    {
                        model.removeRow(i);
                        data2.remove(i);
                        i--;
                    }
                }
            } 
         }
         catch( Exception ex ){}
         return model;
     }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" IN " >
    public DefaultTableModel In( String left, DefaultTableModel model, DefaultTableModel reference, String type)
     {
         try
         {
            if ( reference.getColumnCount() == 0 )
                return model;
            if ( type.equals("Column_Model"))
            {
                ArrayList<String> data = this.GetDataInColumnFromModel(0, reference);
                int index = this.ColToIndex(left, model);
                ArrayList<String> data2 = this.GetDataInColumnFromModel(index, reference);
                for ( int i = 0; i < data2.size();i++)
                {
                    if ( !data.contains(data2.get(i)))
                    {
                        model.removeRow(i);
                        data2.remove(i);
                        i--;
                    }
                }
            } 
         }
         catch( Exception ex ){}
         return model;
     }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" SUM">
    public DefaultTableModel Sum(String left, DefaultTableModel model )
    {
        int index = this.ColToIndex(left, model);
        if ( index <  0)
            return model;
        String tabName;
        if ( !left.contains("."))
             tabName= this.GetNameFromSelectQuery(model.getColumnName(index), true);
        else tabName = this.GetNameFromSelectQuery(left, true);
        int tIndex = this.NameToIndex(tabName);
        if ( tIndex <  0)
            return model;
        left = this.GetNameFromSelectQuery(left, false);
        int colIndex = this.TitleToIndex(tabName, left);
        if ( !main.dataTables.get(tIndex).getColumns().get(colIndex).getType().equals("Number"))
            return model;
        ArrayList<String> colName = new ArrayList<String>();
        for ( int i = 0; i < model.getColumnCount();i++)
        {
            colName.add(model.getColumnName(i));
        }
        colName.add("_SUM_");
        DefaultTableModel newModel = new DefaultTableModel(null, colName.toArray());
        double sum = 0;
        for (int i = 0; i < main.dataTables.get(tIndex).getModel().getRowCount(); i++)
        {
            try
            {
                sum += Double.parseDouble(String.valueOf(main.dataTables.get(tIndex).getModel().getValueAt(i, colIndex)));
            }
            catch( Exception ex ){}
        }
        for (int i = 0; i < model.getRowCount(); i++)
        {
            try
            {
                ArrayList<String> data = this.GetRowData(i, model);
                data.add(String.valueOf(sum));
                newModel.addRow(data.toArray());
            }
            catch( Exception ex ){}
        }
        return newModel;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Count">
    public DefaultTableModel Count(String left, DefaultTableModel model )
    {
        int index = this.ColToIndex(left, model);
        if ( index <  0)
            return model;
        ArrayList<String> colName = new ArrayList<String>();
        for ( int i = 0; i < model.getColumnCount();i++)
        {
            colName.add(model.getColumnName(i));
        }
        colName.add("_Count_");
        DefaultTableModel newModel = new DefaultTableModel(null, colName.toArray());
        int count = 0;
        for (int i = 0; i < model.getRowCount(); i++)
        {
            try
            {
                String v = String.valueOf(model.getValueAt(i, index));
                if ( v.length() > 0 )
                    count ++;
            }
            catch( Exception ex ){}
        }
        for (int i = 0; i < model.getRowCount(); i++)
        {
            try
            {
                ArrayList<String> data = this.GetRowData(i, model);
                data.add(String.valueOf(count));
                newModel.addRow(data.toArray());
            }
            catch( Exception ex ){}
        }
        return newModel;
    }
     //</editor-fold >
    
    //<editor-fold defaultstate="collapsed" desc=" Đếm số cột của bảbarntrong model">
    public int CountColumn( String name, DefaultTableModel model )
    {
        int result = 0;
        for ( int i = 0; i < model.getColumnCount(); i++)
        {
            if ( name.equals(this.GetNameFromSelectQuery(name, true )))
            {
                result++;
            }
        }
        return result;
    }
     //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" tìm vị trí bắt đầu của bảng trong model">
    public int FindStartPoint( String tabName, DefaultTableModel model )
    {
        for ( int i = 0; i < model.getColumnCount(); i++ )
        {
            if ( this.GetNameFromSelectQuery(model.getColumnName(i), true).equals(tabName ))
                return i;
        }
        return -1;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" vi tri cua dong Trong Bang Khac ">
    public int RowContains( int index1, DefaultTableModel model1,String tabName, DefaultTableModel model2)
    {
        int n = CountColumn(  tabName, model1 );
         
        if ( n != model2.getColumnCount())
            return -1;
        int startPoint = FindStartPoint( tabName, model1);
        if ( startPoint == -1 )
            return -1;
        for ( int i = 0; i < model2.getRowCount(); i++)
        {
            if ( this.IsRowEqual(index1, i, model1, startPoint, model2))
                return i;
        }
        return -1;
    }
    public int RowContains( int index1, DefaultTableModel model1, DefaultTableModel model2)
    {
        int n = model1.getColumnCount();
    
        if ( n != model2.getColumnCount())
            return -1;
        for ( int i = 0; i < model2.getRowCount(); i++)
        {
            if ( this.IsRowEqual(index1, i, model1, 0, model2))
            {
                 
                return i;
            }
        }
        return -1;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" So Sanh Row = Nhau ">
    public boolean IsRowEqual( int index1, int index2,DefaultTableModel model1,int startPoint, DefaultTableModel model2)
    {
        int n = model2.getColumnCount();
        for ( int i = 0; i < n; i++)
        {
            String v1 = String.valueOf(model1.getValueAt( index1, startPoint + i));
            String v2 = String.valueOf(model2.getValueAt(index2, i));
          //     main.ShowMessageBox(v1+ "_" + v2, "");
            if ( !v1.equals(v2))
                return false;
        }
        return true;
    }
    //</editor-fold>
   
    //<editor-fold defaultstate="collapsed" desc=" And">
    public DefaultTableModel And( DefaultTableModel models1, DefaultTableModel models2)
    {
        ArrayList<String> colName = new ArrayList<String>();
        for ( int i = 0; i < models1.getColumnCount(); i++ )
        {
            colName.add(models1.getColumnName(i));
        }
        DefaultTableModel model = new DefaultTableModel(null, colName.toArray());
        for ( int j = 0; j < models1.getRowCount(); j++)
        {
            int pos = this.RowContains(j,models1, models2);
            if ( pos >= 0 )
            {
                model.addRow((GetRowData(j, models1)).toArray());
            }
        }
        return model;
    }
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc=" Or ">
    public DefaultTableModel Or( DefaultTableModel models1, DefaultTableModel models2)
    {
        for ( int j = 0; j < models1.getRowCount(); j++)
        {
            int pos = this.RowContains(j,models1, models2);
            if ( pos < 0 )
            {
                models2.addRow(GetRowData(j, models1).toArray());
            }
        }
        return models2;
    }
    //</editor-fold>
    public ArrayList<Node> GetElementByTagName( String name, Node root)
    {
        ArrayList<Node> result = new ArrayList<Node>();
        
        for ( int i = 0; i < root.getChildNodes().getLength(); i++)
        {
            if ( root.getChildNodes().item(i).getNodeName().equals(name))
            {
                result.add( root.getChildNodes().item(i));
            }
        }
        if ( result.size() == 0 )
            result.add(null);
        return result;
    }
    
    //<editor-fold defaultstate="collapsed" desc=" Where ">
    public DefaultTableModel Where( DefaultTableModel model, Node node )
    {
        try
        {
            Node left = node.getChildNodes().item(0);
            while ( left.getNodeType() != Node.ELEMENT_NODE )
                left = left.getNextSibling();
            
            Node right = left.getNextSibling();
            while ( right.getNodeType() != Node.ELEMENT_NODE )
                right = right.getNextSibling();
            String logic = String.valueOf(node.getAttributes().getNamedItem("logic").getNodeValue());
            if ( logic.equals("and"))
            {
                DefaultTableModel temp = this.Clone(model);
                temp = this.Where(temp, left);
                DefaultTableModel temp2 = this.Clone(model);
                temp2 = this.Where(temp2, right);
                model = And(temp, temp2);
//                main.ShowMessageBox(String.valueOf(temp.getRowCount()),"");
//                main.ShowMessageBox(String.valueOf(temp2.getRowCount()),"");
//                main.ShowMessageBox(String.valueOf(model.getRowCount()),"");
            }
            else if ( logic.equals("or"))
            {
                DefaultTableModel temp = this.Clone(model);
                temp = this.Where(temp, left);
                DefaultTableModel temp2 = this.Clone(model);
                temp2 = this.Where(temp2, right);
                model = Or(temp, temp2);
            }
            else if ( logic.equals("="))
            {
                model = this.Equal(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model,
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
               // main.ShowMessageBox(String.valueOf(model.getRowCount()), "");
            }
            else if ( logic.equals(">"))
            {
                
                model = this.GreaterThan(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model, 
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
            }
            else if ( logic.equals("<"))
            {
                model = this.LessThan(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model,
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
            }
            else if ( logic.equals(">=") || logic.equals("!<"))
            {
                model = this.GreaterThanOrEqal(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model,
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
            }
            else if ( logic.equals("<=") || logic.equals("!>"))
            {
                model = this.LessThanOrEqual(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model, 
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
            }
            else if ( logic.equals("<>") || logic.equals("!="))
            {
                model = this.NotEqual(String.valueOf(left.getAttributes().getNamedItem("value").getNodeValue()),
                        String.valueOf(right.getAttributes().getNamedItem("value").getNodeValue()), model,
                        String.valueOf(node.getAttributes().getNamedItem("type").getNodeValue()));
            }
            else if ( logic.equals("IN"))
            {
            }
            else if ( logic.equals("NOT IN"))
            {
            }
            else if ( logic.equals("EXISTS"))
            {
            }
            else if ( logic.equals("NOT EXISTS"))
            {
            }
            return model;
        }
        catch ( Exception ex ){//  main.ShowMessageBox( ex.toString(), "");
        return new DefaultTableModel();}
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc=" thêm dữ liệu ">
    public String InsertInto( Document doc, Node root )
    {
        String result = "Insert Dữ Liệu Thành Công";
        String isHaveColumn= root.getAttributes().getNamedItem("isHaveColumn").getNodeValue();
       
        Node table = this.GetElementByTagName("Table", root).get(0);
        String tName = table.getAttributes().getNamedItem("name").getNodeValue();
        int tIndex = this.NameToIndex(tName);
     
        if ( tIndex < 0 )
            return "Bảng Không Tồn Tại";
        int nCol = main.dataTables.get(tIndex).getModel().getColumnCount();
        int nRow = main.dataTables.get(tIndex).getModel().getRowCount();
         
        if ( nCol < 0 || nRow < 0 )
            return " Cot Không Tồn Tại";
        ArrayList<String> data = new ArrayList<String>();
        for ( int i = 0; i < nCol; i++ )
            data.add("");
        
        try
        {
           
            ArrayList<Node> cols = this.GetElementByTagName("Column", root);
            
            if ( isHaveColumn.equals("Yes"))
            {
                for ( int i = 0;i < cols.size(); i++)
                {
                    String colName = String.valueOf(cols.get(i).getAttributes().getNamedItem("name").getNodeValue());
                    int pos = this.ColToIndex(colName, main.dataTables.get(tIndex).getModel());
                     
                     if ( pos >= 0 && pos < data.size())
                     {
                        String value = String.valueOf(cols.get(i).getAttributes().getNamedItem("value").getNodeValue());
                        data.set(pos, value);
                     }
                }
            }
            else if ( isHaveColumn.equals("No"))
            {
                for ( int i = 0;i < cols.size(); i++)
                {   
                    String value = String.valueOf(cols.get(i).getAttributes().getNamedItem("value").getNodeValue());
                    if ( i > data.size() - 1)
                        break;
                    data.set(i, value);
                }
            }
            main.dataTables.get(tIndex).getModel().addRow(data.toArray());
            
            for ( int i = 0; i < nCol; i ++ )
            {
                String v = String.valueOf( main.dataTables.get(tIndex).getModel().getValueAt( nRow, i));
                boolean success = true;
                if ( main.dataTables.get(tIndex).getColumns().get(i).getType().equals("Number") 
                        && !CheckIsNum(v) )
                {
                    success = false;
                    result = "Vi Pham kiểu dữ liệu";
                }
                if ( !main.dataTables.get(tIndex).CheckNotNull(nRow, i) )
                {
                    success = false;
                    result = "Vi Pham Not Null";
                }
                if ( !main.dataTables.get(tIndex).CheckPrimaryKey(nRow, i))
                {
                    success = false;
                    result = "Vi Pham Trùng lặp khóa chính";
                }
                if ( !CheckForiegnKeyTo( tName, nRow, i))
                {
                    success = false;
                    result = "Vi Pham khóa ngoại";
                }
                if ( success == false )
                {
                    main.dataTables.get(tIndex).getModel().removeRow(nRow);
                    break;
                }
            }
        }
        catch ( Exception ex ){ main.ShowMessageBox(ex.toString(), "");}
        return result ;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Xóa dữ liệu ">
    public String DeleteFrom( Document doc, Node root )
    {
        Node table = this.GetElementByTagName("Table", root).get(0);
        String tabName = table.getAttributes().getNamedItem("name").getNodeValue();
        DefaultTableModel model = new DefaultTableModel();
        int index = this.NameToIndex(tabName);
        if ( index < 0 )
            return "Bảng Không Tồn Tại";
        else
            model = main.dataTables.get(index).Clone();

        DefaultTableModel result  = new DefaultTableModel();
        result = this.Clone(model);
        XPath xpath = XPathFactory.newInstance().newXPath();
        try 
        {
            Node node = this.GetElementByTagName("Where", root).get(0);
            model = this.Where(model, node);
           
            if ( model.getRowCount() == 0)
            {
                return "Delete " + model.getRowCount()+ " dòng dữ liệu";
            }
           
            for ( int i = 0; i < model.getRowCount(); i++)
            {
                int pos = this.RowContains(i, model,tabName, main.dataTables.get(index).getModel());
                
                if ( pos >= 0 )
                {
                    for ( int j = 0; j < model.getColumnCount(); j++ )
                    {
                        if ( !CheckForeignKeyFrom( tabName, pos, j))
                        {
                            return "Lỗi ,Có Khóa Ngoại Trỏ Đến, Không Thể Xóa";
                        }
                        else
                        {
                            main.dataTables.get(index).getModel().removeRow(pos);
                            result.removeRow(pos);
                            break;
                        }
                    }
                }
            }
        } catch ( Exception ex) {  return "Điều kiện xóa không hợp lệ";}
        return "Delete " + model.getRowCount()+ " dòng dữ liệu";
    }
     //</editor-fold >
    
    //<editor-fold defaultstate="collapsed" desc=" Update dữ liệu ">
    public String Update( Document doc, Node root )
    {
        Node table = this.GetElementByTagName("Table", root).get(0);
        String tabName = table.getAttributes().getNamedItem("name").getNodeValue();
        DefaultTableModel model = new DefaultTableModel();
        int index = this.NameToIndex(tabName);
        if ( index < 0 )
            return "Bảng Không Tồn Tại";
        else
            model = main.dataTables.get(index).Clone();
        try 
        {
            Node node = this.GetElementByTagName("Where", root).get(0);
            model = this.Where(model, node);
            if ( model.getColumnCount() == 0)
            {
                return "Update " + model.getRowCount()+ " dòng dữ liệu";
            }
           
            ArrayList<Node> sets = this.GetElementByTagName("Set", root);
            for ( int i = 0; i < sets.size(); i++ )
            {
                String colName = sets.get(i).getAttributes().getNamedItem("name").getNodeValue();
                String value = sets.get(i).getAttributes().getNamedItem("value").getNodeValue();
                int colIndex = this.TitleToIndex(tabName, colName);
       
                 if ( colIndex < 0)
                     continue;
                if ( main.dataTables.get(index).getColumns().get(colIndex).getType().equals("Number")
                        && !CheckIsNum(value))
                    return value + ":  Kiểu Dữ Liệu Không Đúng";
                for ( int j = 0; j < model.getRowCount(); j++)
                {
                    int pos = RowContains( j, model, tabName,main.dataTables.get(index).getModel());
                    String oldValue = String.valueOf(main.dataTables.get(index).getModel().getValueAt( pos, colIndex));
                    if ( pos >= 0 )
                    {   
                       // main.ShowMessageBox(String.valueOf(pos), "");
                        main.dataTables.get(index).getModel().setValueAt(value, pos, colIndex);
                        model.setValueAt(value, j, colIndex);
                        if ( !main.dataTables.get(index).CheckPrimaryKey(pos, colIndex))
                        { 
                            main.dataTables.get(index).getModel().setValueAt(oldValue, pos, colIndex);
                             model.setValueAt(oldValue, j, colIndex);
                            return "lỗi, Trùng Lăp khóa chính";
                        }
                        if ( !this.CheckForeignKeyFrom(tabName, pos, colIndex))
                        { 
                            main.dataTables.get(index).getModel().setValueAt(oldValue, pos, colIndex);
                             model.setValueAt(oldValue, j, colIndex);
                            return "Có Khóa Ngoại Trỏ Đến, Không Thể Update";
                        }
                        if ( !this.CheckForiegnKeyTo(tabName, pos, colIndex))
                        {
                            main.dataTables.get(index).getModel().setValueAt(oldValue, pos, colIndex);
                             model.setValueAt(oldValue, j, colIndex);
                            return "Tro đến một bảng khác, không Không Thể Update";
                        }
                    }
                }
            }
            
            }catch ( Exception ex) {// main.ShowMessageBox(ex.toString(), "");
            return "Điều kiện Update không hợp lệ";}
        return "Update " + model.getRowCount()+ " dòng dữ liệu";
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Tính tích dề các của 2 bảng ">
    public DefaultTableModel CreateDecard( DefaultTableModel model1, DefaultTableModel model2)
    {
        ArrayList<String> listCol = new ArrayList<String>();
        for( int i = 0; i < model1.getColumnCount(); i++ )
        {
           listCol.add(model1.getColumnName(i));
        }
        for ( int j = 0; j < model2.getColumnCount(); j++)
        {
            listCol.add(model2.getColumnName(j));
        }
        DefaultTableModel table = new DefaultTableModel(null, listCol.toArray());
        for ( int i = 0; i < model1.getRowCount(); i ++)
        {
            if ( model2.getRowCount() > 0 )
            {
                for ( int j = 0; j < model2.getRowCount(); j++ )
                {
                    ArrayList<String> data = GetRowData( i, model1);
                    data.addAll(GetRowData(j, model2));
                    table.addRow(data.toArray());
                }
            }
            else
            {
                ArrayList<String> data = GetRowData( i, model1);
                for ( int j = 0; j < model2.getColumnCount(); j++ )
                {
                    data.add("");
                }
                table.addRow(data.toArray());
            }
        }
        return table; 
    }
    //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" lấy dữ liệu từ 1 dòng của model ">
    public ArrayList<String> GetRowData( int index, DefaultTableModel model )
    {
        ArrayList<String> result = new ArrayList<String>();
        for ( int i = 0; i < model.getColumnCount(); i++)
        {
            if ( index >= model.getRowCount())
                result.add("");
            else
                result.add(String.valueOf(model.getValueAt(index, i)));
        }
        return result;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Select ">
    public DefaultTableModel Select( Document doc, Node root )
    {
        DefaultTableModel model = new DefaultTableModel();
        try
        {
            ArrayList<Node> tables = this.GetElementByTagName("From", root);
             ArrayList<String> listName= new ArrayList<String>();
            ArrayList<DefaultTableModel> models= new ArrayList<DefaultTableModel>();
            ArrayList<String> listJoin= new ArrayList<String>();
            for ( int i = 0 ; i < tables.size(); i++ )
            {
                String tabName = tables.get(i).getAttributes().getNamedItem("tabName").getNodeValue();
                listName.add(tabName);
                listJoin.add(tables.get(i).getAttributes().getNamedItem("type").getNodeValue());
                int index = this.NameToIndex(tabName);
                
                if ( index < 0 )
                    return new DefaultTableModel();
                else
                    models.add(main.dataTables.get(index).Clone());
            }
            model = models.get(0);
            for( int i = 1 ; i < models.size(); i++ )
            {
                model = this.CreateDecard(model, models.get(i));
               
            }
            try 
            {
                Node node = this.GetElementByTagName("Where", root).get(0);
                if ( node != null )
                    model = this.Where(model, node);
                }catch ( Exception ex) { main.ShowMessageBox(ex.toString(), "");return new DefaultTableModel();}
            
        }
        catch( Exception ex ){}
        return model;
    }
     //</editor-fold >
     //<editor-fold defaultstate="collapsed" desc=" Lấy Danh sách cột ">
    public ArrayList<String>  GetColumnList( Node root )
    {
     
           ArrayList<Node> columns = this.GetElementByTagName("Column", root);
            ArrayList<String> listColName= new ArrayList<String>();
            if( columns.size() >= 1 && !columns.get(0).getAttributes().getNamedItem("name").getNodeValue().equals("*"))
            {
                for ( int i = 0 ; i < columns.size(); i++ )
                {
                    listColName.add(columns.get(i).getAttributes().getNamedItem("name").getNodeValue()); 
                }
            }
            return listColName;
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Lấy Danh sách cột với aslias name ">
    public ArrayList<String>  GetColumnAs( Node root )
    {
           ArrayList<Node> columns = this.GetElementByTagName("Column", root);
            ArrayList<String> listColAs= new ArrayList<String>();
            if( columns.size() >= 1 && !columns.get(0).getAttributes().getNamedItem("name").getNodeValue().equals("*"))
            {
                for ( int i = 0 ; i < columns.size(); i++ )
                {
                    String as = columns.get(i).getAttributes().getNamedItem("as").getNodeValue();
                    if ( as.length() > 0 )
                        listColAs.add(""); 
                    else
                        listColAs.add(columns.get(0).getAttributes().getNamedItem("name").getNodeValue()); 
                }
            }
            return listColAs;
    }
     //</editor-fold >
    
    //<editor-fold defaultstate="collapsed" desc=" Trả KQ về cho client ">
    public int IsSelected( ArrayList<String> listColName, String col )
    {
        for ( int i = 0 ; i < listColName.size(); i++ )
        {
            if( listColName.get(i).equals(this.GetNameFromSelectQuery(col, false)) 
                || listColName.get(i).equals(col) )
                 return i;
        }
        return -1;
    }
    public Node Return(Document doc,  Node root, String type, String result,  DefaultTableModel model,ArrayList<String> listColName, ArrayList<String> listColAs)
    {
        try {
            if ( type.equals("String"))
            {
                if ( result.startsWith("Delete") || result.startsWith("Update") || result.startsWith("Insert") )
                {
                    Element suc = doc.createElement("Success");
                    suc.setAttribute("value", result );
                    root.appendChild(suc);
                }
                else
                {
                    Element suc  = doc.createElement("Error");
                    suc.setAttribute("value", result );
                    root.appendChild(suc);
                }
            }
            else if ( type.equals("Model"))
            {
               
                 Element tab  = doc.createElement("Table");
                for ( int i= 0; i < model.getColumnCount(); i++)
                {  
                    Element col = doc.createElement("Column");
                    
                    if ( listColName.size() == 0)
                    {
                        col.setAttribute("name", model.getColumnName(i));
                        for ( int j = 0; j < model.getRowCount(); j++)
                        {
                            String x = String.valueOf(model.getValueAt(j, i));
                            if ( x.contains("&"))
                                x.replace("&", "&amp;");
                            if ( x.contains("<"))
                                x.replace("<", "&lt;");
                            if ( x.contains("\""))
                                x.replace("\"", "&quot;");
                            if ( x.contains("\'"))
                                x.replace("\'", "&#39;");
                            Element row = doc.createElement("row");
                            row.setAttribute("value", x);
                            col.appendChild(row);
                        }
                        tab.appendChild(col);
                    }
                  
                    else if (IsSelected( listColName, model.getColumnName(i) ) >= 0)
                    {
                      //  main.ShowMessageBox(listColName.get(0)+ "_"+ model.getColumnName(i), type);
                        if ( listColAs.get(IsSelected( listColName, model.getColumnName(i) )).length() > 0 )
                            col.setAttribute("name", listColAs.get(IsSelected( listColName, model.getColumnName(i) )));
                        else
                            col.setAttribute("name", listColName.get(IsSelected( listColName, model.getColumnName(i) )));
                        for ( int j = 0; j < model.getRowCount(); j++)
                        {
                            String x = String.valueOf(model.getValueAt(j, i));
                            if ( x.contains("&"))
                                x.replace("&", "&amp;");
                            if ( x.contains("<"))
                                x.replace("<", "&lt;");
                            if ( x.contains("\""))
                                x.replace("\"", "&quot;");
                            if ( x.contains("\'"))
                                x.replace("\'", "&#39;");
                            Element row = doc.createElement("row");
                            row.setAttribute("value", x);
                            col.appendChild(row);
                        }
                        tab.appendChild(col);
                    }
                }
                root.appendChild(tab);
            }
            return root;
        }
        catch ( Exception ex) {
            return root;
        }
    }
     //</editor-fold >
    //<editor-fold defaultstate="collapsed" desc=" Xử Lý Câu Truy vấn ">
    public Document Annalyze( Document doc)
    {
        ArrayList<String> listColName= new ArrayList<String>();
         ArrayList<String> listColAs= new ArrayList<String>();
        Document docResult = null;
        try {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            fac.setNamespaceAware(true);
            DocumentBuilder documentBuilder = null;
                documentBuilder = fac.newDocumentBuilder();
                docResult = documentBuilder.newDocument();
                doc.getDocumentElement().normalize();
                Node nodeResult = docResult.createElement("SqlStatement");
                NodeList listQuery = doc.getElementsByTagName("Query");
                for ( int i = 0; i < listQuery.getLength(); i++)
                {
                    Node root = listQuery.item(i);
                    String command = root.getAttributes().getNamedItem("name").getNodeValue();
                    
                    try
                    {
                        DefaultTableModel model = new DefaultTableModel();
                        String result ="";
                        if ( command.equals("Insert"))
                        {
                            result = InsertInto(doc, root);
                            nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Update"))
                        {
                            result = Update(doc, root);
                           nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Delete"))
                        {
                            result = DeleteFrom( doc, root);
                           nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Select"))
                        {
                            model = Select(doc, root);
                            listColName = this.GetColumnList(root);
                            listColAs = this.GetColumnAs(root);
                            
                             
                            nodeResult = Return (docResult, nodeResult, "Model", result, model, listColName,listColAs);
                        }
                    }
                    catch( Exception ex )
                    {
                        main.ShowMessageBox(ex.toString(), "");
                    }
                }
                docResult.appendChild(nodeResult);
                return docResult;
                } catch ( Exception ex) {

                }
            return doc; 
        }
    //</editor-fold >
    public Document Test()
    {
        Document doc = null;
        Document docResult = null;
        ArrayList<String> listColName= new ArrayList<String>();
         ArrayList<String> listColAs= new ArrayList<String>();
        try {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            fac.setNamespaceAware(true);
            DocumentBuilder documentBuilder = null;
            
                documentBuilder = fac.newDocumentBuilder();
                doc = documentBuilder.parse( new File( "D://test.xml" ));
                docResult = documentBuilder.newDocument();
                doc.getDocumentElement().normalize();
                
                Node nodeResult = docResult.createElement("SQL");
                
                NodeList listQuery = doc.getElementsByTagName("Query");
                for ( int i = 0; i < listQuery.getLength(); i++)
                {
                    Node root = listQuery.item(i);
                    String command = root.getAttributes().getNamedItem("name").getNodeValue();
                    try
                    {
                        DefaultTableModel model = new DefaultTableModel();
                        String result ="";
                        if ( command.equals("Insert"))
                        {
                            result = InsertInto(doc, root);
                            nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Update"))
                        {
                            result = Update(doc, root);
                           nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Delete"))
                        {
                            result = DeleteFrom( doc, root);
                           nodeResult = Return (docResult, nodeResult, "String", result, model, listColName,listColAs);
                        }
                        else if ( command.equals("Select"))
                        {
                            model = Select(doc, root);
                            listColName = this.GetColumnList(root);
                            listColAs = this.GetColumnAs(root);
                            // main.ShowMessageBox(String.valueOf(listColName.get(0)), "");
                            nodeResult = Return (docResult, nodeResult, "Model", result, model, listColName,listColAs);
                        }
                    }
                    catch( Exception ex )
                    {
                        main.ShowMessageBox(ex.toString(), "");
                    }
                }
               
                docResult.appendChild(nodeResult);
                return docResult;
                } catch (SAXException ex) {
                    Logger.getLogger(ToolSQL.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IOException ex) {
                    Logger.getLogger(ToolSQL.class.getName()).log(Level.SEVERE, null, ex);
                } catch (ParserConfigurationException ex) {

                }
            return doc; 
        }
}

